﻿using System;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using KlauserswagModel;

namespace WebShopDAL
{
    public class UserDB
    {
        private static KlauserswagEntities _context = new KlauserswagEntities();
        //Create:
        /// <summary>
        /// Method for inserting a new User into the Database.
        /// </summary>
        /// <param name="user">The user to be inserted.</param>
        /// <returns>Returns 1 if everything went well.</returns>
        public static int InsertNewUser(User user)
        {
            try
            {
                _context.Users.Add(user);
                return _context.SaveChanges();

                //using (var connection = ConnectionDB.GetConnection())
                //{
                //    if (connection == null)
                //        throw new Exception("Connection is null in UserDB, InsertNewUser");

                //    var sqlQuery = new StringBuilder("INSERT INTO [dbo].[Users]([Username],[Password],[Firstname],[Lastname],[Personnummer],[Address],[Zip],[City],[Email],[Phone])");
                //    sqlQuery.Append("VALUES(@Username, @Password, @Firstname, @Lastname, @Personnummer, @Address, @Zip, @City, @Email, @Phone)");

                //    using (var command = new SqlCommand(sqlQuery.ToString(), connection))
                //    {
                //        command.Parameters.Add("Username", SqlDbType.NChar).Value = user.Username;
                //        command.Parameters.Add("Password", SqlDbType.NChar).Value = user.Password;
                //        command.Parameters.Add("Firstname", SqlDbType.NChar).Value = user.FirstName;
                //        command.Parameters.Add("Lastname", SqlDbType.NChar).Value = user.LastName;
                //        command.Parameters.Add("Personnummer", SqlDbType.NChar).Value = user.Personnumber;
                //        command.Parameters.Add("Address", SqlDbType.NChar).Value = user.Adress;
                //        command.Parameters.Add("Zip", SqlDbType.NChar).Value = user.Zip;
                //        command.Parameters.Add("City", SqlDbType.NChar).Value = user.City;
                //        command.Parameters.Add("Email", SqlDbType.NChar).Value = user.Email;
                //        command.Parameters.Add("Phone", SqlDbType.NChar).Value = user.Phone;

                //        int affectedRows = command.ExecuteNonQuery();
                //        return affectedRows;
                //    }
                //}
            }
            catch (Exception ex)
            {
                throw new KlauserSwagException("Something went wrong in UserDB, InsertNewUser", ex);
            }
        }

        //Read:
        /// <summary>
        /// Method for retrieving a User on a specific ID.
        /// </summary>
        /// <param name="userId">The ID to retrieve the User on.</param>
        /// <returns>Returns the User on the specified ID</returns>
        public static User GetUser(int userId)
        {
            try
            {
                return _context.Users.Where(u => u.UserID == userId).ToList().First();

                //using (var connection = ConnectionDB.GetConnection())
                //{
                //    if (connection == null)
                //        throw new Exception("Connection is null in UserDB, GetUser");

                //    var sqlQuery = new StringBuilder("SELECT [UserID],[Username],[Password],[Firstname],[Lastname],[Personnummer],[Address],[Zip],[City],[Email],[Phone]");
                //    sqlQuery.Append("FROM [dbo].[Users]");
                //    sqlQuery.Append("WHERE [UserID] = @UserID");

                //    using (var command = new SqlCommand(sqlQuery.ToString(), connection))
                //    {
                //        command.Parameters.Add("UserID", SqlDbType.BigInt).Value = userId;

                //        var adapter = new SqlDataAdapter(command);
                //        var dataSet = new DataSet();
                //        adapter.Fill(dataSet);

                //        bool userFound = dataSet.Tables[0].Rows.Count == 1;

                //        if (userFound)
                //        {
                //            return new User(username: dataSet.Tables[0].Rows[0]["Username"].ToString().Trim(),
                //                password: dataSet.Tables[0].Rows[0]["Password"].ToString().Trim(),
                //                firstname: dataSet.Tables[0].Rows[0]["Firstname"].ToString().Trim(),
                //                lastname: dataSet.Tables[0].Rows[0]["Lastname"].ToString().Trim(),
                //                personnumber: dataSet.Tables[0].Rows[0]["Personnummer"].ToString().Trim(),
                //                adress: dataSet.Tables[0].Rows[0]["Address"].ToString().Trim(),
                //                zip: dataSet.Tables[0].Rows[0]["Zip"].ToString().Trim(),
                //                city: dataSet.Tables[0].Rows[0]["City"].ToString().Trim(),
                //                email: dataSet.Tables[0].Rows[0]["Email"].ToString().Trim(),
                //                phone: dataSet.Tables[0].Rows[0]["Phone"].ToString().Trim(),
                //                userId: userId
                //                );
                //        }
                //        else
                //        {
                //            return null;
                //        }
                //    }
                //}
            }
            catch (Exception ex)
            {
                throw new KlauserSwagException("Something went wrong in UserDB, GetUser", ex);
            }
        }

        /// <summary>
        /// Method for retrieving a User based on input.
        /// </summary>
        /// <param name="username">Username of User</param>
        /// <param name="password">Password for User</param>
        /// <returns>User information</returns>
        public static User GetCurrentUser(string username, string password)
        {
            try
            {
                return _context.Users.Where(u => u.Username == username && u.Password == password).ToList().First();

                //using (var connection = ConnectionDB.GetConnection())
                //{
                //    if (connection == null)
                //        throw new Exception("Connection is null in UserDB, GetCurrentUser");

                //    var sqlQuery = new StringBuilder("SELECT [UserID],[Username],[Firstname],[Lastname],[Personnummer],[Address],[Zip],[City],[Email],[Phone]");
                //    sqlQuery.Append("FROM [dbo].[Users]");
                //    sqlQuery.Append("WHERE [Username] = @Username AND [Password] = @Password");

                //    using (var command = new SqlCommand(sqlQuery.ToString(), connection))
                //    {
                //        command.Parameters.Add("Username", SqlDbType.NChar).Value = username;
                //        command.Parameters.Add("Password", SqlDbType.NChar).Value = password;

                //        var adapter = new SqlDataAdapter(command);
                //        var dataSet = new DataSet();
                //        adapter.Fill(dataSet);

                //        bool userFound = dataSet.Tables[0].Rows.Count == 1;

                //        if (userFound)
                //        {
                //            return new User(username: dataSet.Tables[0].Rows[0]["Username"].ToString().Trim(),
                //                firstname: dataSet.Tables[0].Rows[0]["Firstname"].ToString().Trim(),
                //                lastname: dataSet.Tables[0].Rows[0]["Lastname"].ToString().Trim(),
                //                personnumber: dataSet.Tables[0].Rows[0]["Personnummer"].ToString().Trim(),
                //                adress: dataSet.Tables[0].Rows[0]["Address"].ToString().Trim(),
                //                zip: dataSet.Tables[0].Rows[0]["Zip"].ToString().Trim(),
                //                city: dataSet.Tables[0].Rows[0]["City"].ToString().Trim(),
                //                email: dataSet.Tables[0].Rows[0]["Email"].ToString().Trim(),
                //                phone: dataSet.Tables[0].Rows[0]["Phone"].ToString().Trim(),
                //                userId: int.Parse(dataSet.Tables[0].Rows[0]["UserID"].ToString())
                //                );
                //        }
                //        else
                //        {
                //            return null;
                //        }
                //    }
                //}
            }
            catch (Exception ex)
            {
                throw new KlauserSwagException("Something went wrong in UserDB, GetCurrentUser", ex);
            }
        }

        /// <summary>
        /// Method for checking if input Username and Password exists in Database.
        /// </summary>
        /// <param name="username">The username of the user</param>
        /// <param name="password">The password which belongs to the username</param>
        /// <returns>If username and password match, returns TRUE else FALSE</returns>
        public static bool IsLoggedIn(string username, string password)
        {
            try
            {
                var query = _context.Users.Where(u => u.Username == username && u.Password == password).ToList().First();
                return query != null;

                //using (var connection = ConnectionDB.GetConnection())
                //{
                //    if (connection == null)
                //        throw new Exception("Connection is null in UserDB, IsLoggedIn");

                //    var sqlQuery = new StringBuilder("SELECT [Username],[Password] ");
                //    sqlQuery.Append("FROM [dbo].[Users]");
                //    sqlQuery.Append("WHERE [Username] = @Username AND [Password] = @Password");

                //    using (var command = new SqlCommand(sqlQuery.ToString(), connection))
                //    {
                //        command.Parameters.Add("Username", SqlDbType.NChar).Value = username;
                //        command.Parameters.Add("Password", SqlDbType.NChar).Value = password;

                //        var adapter = new SqlDataAdapter(command);
                //        var dataSet = new DataSet();
                //        adapter.Fill(dataSet);

                //        bool userLoggedIn = dataSet.Tables[0].Rows.Count > 0;

                //        if (userLoggedIn)
                //        {
                //            return true;
                //        }
                //        else
                //            return false;
                //    }
                //}
            }
            catch (Exception ex)
            {
                throw new KlauserSwagException("Something went wrong in UserDB, IsLoggedIn", ex);
            }
        }

        //Update:
        /// <summary>
        /// Method for updating a User.
        /// </summary>
        /// <param name="user">The User to update information on.</param>
        /// <returns>Returns 1 if everything went well.</returns>
        public static int UpdateUser(User user)
        {
            try
            {
                var query = _context.Users.Where(u => u.UserID == user.UserID);
                return _context.SaveChanges();

                //using (var connection = ConnectionDB.GetConnection())
                //{
                //    if (connection == null)
                //        throw new Exception("Connection is null in UserDB, UpdateUser");

                //    var sqlQuery = new StringBuilder("UPDATE [dbo].[Users]");
                //    sqlQuery.Append("SET[Username] = @Username,[Password] = @Password,[Firstname] = @Firstname,[Lastname] = @Lastname,[Personnummer] = @Personnummer,[Address] = @Address,[Zip] = @Zip,[City] = @City,[Email] = @Email,[Phone] = @Phone ");
                //    sqlQuery.Append("WHERE [UserID] = @UserID");

                //    using (var command = new SqlCommand(sqlQuery.ToString(), connection))
                //    {
                //        command.Parameters.Add("Username", SqlDbType.NChar).Value = user.Username;
                //        command.Parameters.Add("Password", SqlDbType.NChar).Value = user.Password;
                //        command.Parameters.Add("Firstname", SqlDbType.NChar).Value = user.FirstName;
                //        command.Parameters.Add("Lastname", SqlDbType.NChar).Value = user.LastName;
                //        command.Parameters.Add("Personnummer", SqlDbType.NChar).Value = user.Personnumber;
                //        command.Parameters.Add("Address", SqlDbType.NChar).Value = user.Adress;
                //        command.Parameters.Add("Zip", SqlDbType.NChar).Value = user.Zip;
                //        command.Parameters.Add("City", SqlDbType.NChar).Value = user.City;
                //        command.Parameters.Add("Email", SqlDbType.NChar).Value = user.Email;
                //        command.Parameters.Add("Phone", SqlDbType.NChar).Value = user.Phone;
                //        command.Parameters.Add("UserID", SqlDbType.BigInt).Value = user.UserId;

                //        int affectedRows = command.ExecuteNonQuery();
                //        return affectedRows;
                //    }
                //}
            }
            catch (Exception ex)
            {
                throw new KlauserSwagException("Something went wrong in UserDB, UpdateUser", ex);
            }
        }

        //Delete:
        /// <summary>
        /// Method for deleting a User from the Database.
        /// </summary>
        /// <param name="userId">The ID of the User to delete.</param>
        /// <returns>Returns 1 if everything went well.</returns>
        public static int DeleteUser(int userId)
        {
            try
            {
                var query = _context.Users.Where(u => u.UserID ==userId);
                return _context.SaveChanges();

                //using (var connection = ConnectionDB.GetConnection())
                //{
                //    if (connection == null)
                //        throw new Exception("Connection is null in UserDB, DeleteUser");

                //    var sqlQuery = new StringBuilder("DELETE FROM [dbo].[Users] WHERE [UserID] = @UserID");

                //    using (var command = new SqlCommand(sqlQuery.ToString(), connection))
                //    {
                //        command.Parameters.Add("UserID", SqlDbType.BigInt).Value = userId;

                //        int affectedRows = command.ExecuteNonQuery();
                //        return affectedRows;
                //    }
                //}
            }
            catch (Exception ex)
            {
                throw new KlauserSwagException("Something went wrong in UserDB, DeleteUser", ex);
            }
        }

    }
}
